/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Golf_Manager.sqlite;

import Golf_Manager.sqlite.sql.MemberSql;
import java.sql.*;
import java.util.List;
import Golf_Manager.vo.Member;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class MemberDAO {
    
    @SuppressWarnings("empty-statement")
    public static List<Member> selectMember(StringBuilder whrerSql, String orderString){
        List<Member> members = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
          
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:data.sqlite");
            pstmt = connection.prepareStatement(MemberSql.getSelectMemberSql(whrerSql, orderString).toString());
            rs = pstmt.executeQuery();
            
            while(rs.next()){
                members.add(new Member(rs.getString("MEMBER_SEQ"), rs.getString("NAME"), rs.getString("BIRTH") ,rs.getString("AGE"), 
                                       rs.getString("GENDER"), rs.getString("COMPANYNAME") ,rs.getString("HAND") ,rs.getString("GRADE"), 
                                       rs.getString("JOINYN"), rs.getString("NOTE1"), rs.getString("NOTE2"), rs.getString("NOTE3"), "N"));
            }
            
        } catch (SQLException | ClassNotFoundException e){
            Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            if ( rs != null ) try { rs.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( pstmt != null ) try { pstmt.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( connection != null ) try { connection.close(); } catch (SQLException e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        }
        
        return members;
    }
    
    @SuppressWarnings("empty-statement")
    public static int saveMember(List<Member> saveMembers){
        List<Member> insertMembers = new ArrayList<>();
        List<Member> updateMembers = new ArrayList<>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int effectRowCnt = 0;
        
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:data.sqlite");
            connection.setAutoCommit(false);
                
            for(Member member : saveMembers){
                if(member.getFlag().equals("Y")) {
                    if(member.getMemberSeq().equals("")){
                        insertMembers.add(member);
                    } else {
                        updateMembers.add(member);
                    }
                }
            }
            
            if(insertMembers.size() > 0 || updateMembers.size() > 0){
                
                for(int rIdx = insertMembers.size()-1; rIdx >= 0; rIdx-- ){
                    pstmt = connection.prepareStatement(MemberSql.getInsertMemberSql(insertMembers.get(rIdx)).toString());
                    effectRowCnt += pstmt.executeUpdate();
                }    
                
                
                
                for(Member updateMember : updateMembers){
                    pstmt = connection.prepareStatement(MemberSql.getUpdateMemberSql(updateMember).toString());
                    effectRowCnt += pstmt.executeUpdate();
                }
                
                connection.commit();
                
            }
          
        } catch (ClassNotFoundException | SQLException e){
            try { if ( connection != null ) connection.rollback(); } catch (Exception ex) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        } finally {
            if ( rs != null ) try { rs.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( pstmt != null ) try { pstmt.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( connection != null ) try { connection.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        }
        return effectRowCnt;
    }
    
    @SuppressWarnings("empty-statement")
    public static int deleteMember(String  delMemberSeqs) {
        
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int effectRowCnt = 0;
        
        try {
            
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:data.sqlite");
            connection.setAutoCommit(false);
            
            pstmt = connection.prepareStatement(MemberSql.getDeleteMemberSql(delMemberSeqs).toString());
            effectRowCnt += pstmt.executeUpdate(); 
            connection.commit();
                
        } catch (ClassNotFoundException | SQLException e){
            try { if ( connection != null ) connection.rollback(); } catch (Exception ex) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        } finally {
            if ( rs != null ) try { rs.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( pstmt != null ) try { pstmt.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
            if ( connection != null ) try { connection.close(); } catch (Exception e) { Logger.getLogger(MemberDAO.class.getName()).log(Level.SEVERE, null, e); };
        }
        return effectRowCnt;
    }
    
}
